import re
import time
import json
from flask import request
from flask_restful import abort
from flask_login import current_user, login_user
from funcy import project
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import IntegrityError
from funcy import partial
import logging
from redash import models, limiter
from redash.permissions import (
    require_default,
)
from redash.handlers.base import (
    BaseResource,
    require_fields,
    get_object_or_404,
    paginate,
    order_results as _order_results,
)

from redash.authentication.account import (
    invite_link_for_user,
    send_invite_email,
    send_password_reset_email,
    send_verify_email,
)
from redash.settings import parse_boolean
from redash import settings

class CustomerResource(BaseResource):
    def get(self, code):
        try:
            customers = models.Customers.get_by_code(code,self.current_org)
            return customers.to_dict()
        except IntegrityError as e:
            logging.debug("CustomerResource: %s", str(e))
            abort(503, message= str(e))

class CustomersListResource(BaseResource):
    def get(self,bytstatus):
        try:
            results = models.Customers.get_by_status(bytstatus,self.current_org).order_by(models.Customers.strcustomercode.asc())
            return [t.to_dict() for t in results]
        except IntegrityError as e:
            logging.debug("CustomersListResource: %s", str(e))
            abort(503, message= str(e))

class CustomerNewResource(BaseResource):
    def get(self):
        result = models.Customers.get_id()
        return result[0]

    @require_default
    def post(self):
        req = request.get_json(force=True)
        require_fields(req, ("strcustomercode","strcustomername"))
        customers = models.Customers(
        lngcustomerid = 0,
        org=self.current_org,
        strcustomercode = req["strcustomercode"],
        strcustomername = req["strcustomername"],
        lngcustomertypeid =0,
        strcustomertypecode = '',
        strcustomertypename = '',
        representative =  req["representative"],
        mobile =  req["mobile"],
        address =  req["address"],
        blnisinactive = req["blnisinactive"],
        userid = req["userid"],
        bytstatus = 2,
        blnchuli = 0,
        )

        hrpsql="""
            insert into customer(LNGCUSTOMERID, STRCUSTOMERCODE, STRCUSTOMERNAME, STRCATEGORY, LNGCUSTOMERTYPEID, 
            BLNISINACTIVE, LNGTERMID, DBLCREDITLIMIT, STRLASTFCDATE, LNGLASTFCRECEIPTID, 
            DBLDISCOUNTRATE, LNGEMPLOYEEID, LNGAREAID, STRCONTACTNAME, LNGTITLEID, 
            STROFFICEPHONENUMBER, STRHOMEPHONENUMBER, STRFAXNUMBER,  STREMAIL, STRTAXNO, 
            STRBILLTOADDRESS, STRBILLTOPOSTALCODE, STRLICENCENO, STRELICENCENO, STRCERTIFICATE,
            STRNOTES, STRSTARTDATE, LNGARACCOUNTID, LNGAPACCOUNTID, LNGARDISCOUNTACCOUNTID, 
            LNGAPDISCOUNTACCOUNTID, BLNISPAYDISCOUNT, BLNISQUANDISCOUNT, BLNISSALEDISCOUNT, BLNISALLITEM,
            STRITEMCODESTART, STRITEMCODEEND, STRCREATEDATE, STRCLOSEDATE, STRCUSTOMERAUXCODE, 
            LNGPRICEGRADEID, STRLICENCEDATE, STRBUSINESSLICENCEDATE, STRCHECKOUTDATE, BLNPREAPPROVE, 
            BLNCUSTOMERPREAPPROVE, BLNSUPPLYPREAPPROVE, STRLICENCEMSG, STRQUALITYPMSG, STRMOVEPHONE, 
            STRCARDNO, STRBIMID, BLNSHOWEINFORMATION, LNGACCOUNTSUBJECTTYPEID)
            select customer_seq.nextval, '"""+req["strcustomercode"]+"""', '"""+req["strcustomername"]+"""', '1', (select nvl(max(lngcustomertypeid),0) From customertype where blnisdetail=1 and blnisinactive=0), 
            0, 0, 0, ' ', 0, 
            100, 0, 0, ' ', 0, 
            ' ', ' ', ' ', ' ', ' ',
            nvl('"""+req["address"]+"""',' '), ' ', ' ', ' ', ' ', 
            ' ', '2022-06-26', 0, 560, 0, 
            0, 0, 0, 0, 1,
            ' ', ' ', '2022-06-26', ' ', ' ',
            0, '2037-04-10', '2024-12-29', ' ', 0,
            0, 0, ' ', ' ', ' ',
            ' ', ' ', 1, 7 from dual WHERE NOT EXISTS(SELECT 'X' FROM CUSTOMER WHERE STRCUSTOMERCODE='"""+req["strcustomercode"]+"""')"""
        strsql = """ select strcustomercode From public.customers where strcustomername='"""+req["strcustomername"]+"""' """
        try:
            result = models.Enums.get_selsql(strsql)
            if result is None :
                try:
                    models.db.session.add(customers)
                    models.db.session.commit()
                except IntegrityError as e:
                    if "strcustomercode" in str(e):
                        abort(400, message="单位编号已存在.")
                    abort(500)
            else :
                abort(400, message="单位名称已存在.")
        except :
            abort(400, message="单位名称已存在.")
        strsql = """select  value from public.globalsetting where  type='接口' and code='合同单位回写HRP' and value ='1'"""
        try:
            result = models.Enums.get_selsql(strsql)
            if result is None :
                return ;
        except :
            abort(400, message=strsql)
        strsql=""" select  strcustomername from customer
        where strcustomercode = '"""+req["strcustomercode"]+"""'"""
        try:
            data_source = models.DataSource.get_by_name("jsphrp")
            result, error = data_source.query_runner.run_query(strsql, self.current_user)
            if error :
                return str(error);
            json_object = json.loads(result)
            if len(json_object["rows"])>0 :
                strsql=""" update customer set strcustomername='"""+req["strcustomername"]+"""' 
                where strcustomercode = '"""+req["strcustomercode"]+"""'"""
                data_source = models.DataSource.get_by_name("jsphrp")
                result, error = data_source.query_runner.run_query(strsql, self.current_user)
                return ;
        except :
            return;
        try:
            data_source = models.DataSource.get_by_name("jsphrp")
            result, error = data_source.query_runner.run_query(hrpsql, self.current_user)
        except :
            return;


class CustomersResource(BaseResource):
    def get(self, customers_id):
        try:
            result = models.Customers.get_by_id(customers_id)
            return result.to_dict()
        except IntegrityError as e:
            logging.debug("CustomersResource: %s", str(e))
            abort(503, message= str(e))

    @require_default
    def post(self, customers_id):
        req = request.get_json(force=True)
        require_fields(req, ("strcustomercode","strcustomername"))

        customers = models.Customers.get_by_id(customers_id)
        params = dict(strcustomercode = req["strcustomercode"],  strcustomername = req["strcustomername"],
                representative =  req["representative"],mobile =  req["mobile"],address =  req["address"],
                userid = req["userid"],
                blnisinactive = req["blnisinactive"])

        hrpsql="""
            insert into customer(LNGCUSTOMERID, STRCUSTOMERCODE, STRCUSTOMERNAME, STRCATEGORY, LNGCUSTOMERTYPEID, 
            BLNISINACTIVE, LNGTERMID, DBLCREDITLIMIT, STRLASTFCDATE, LNGLASTFCRECEIPTID, 
            DBLDISCOUNTRATE, LNGEMPLOYEEID, LNGAREAID, STRCONTACTNAME, LNGTITLEID, 
            STROFFICEPHONENUMBER, STRHOMEPHONENUMBER, STRFAXNUMBER,  STREMAIL, STRTAXNO, 
            STRBILLTOADDRESS, STRBILLTOPOSTALCODE, STRLICENCENO, STRELICENCENO, STRCERTIFICATE,
            STRNOTES, STRSTARTDATE, LNGARACCOUNTID, LNGAPACCOUNTID, LNGARDISCOUNTACCOUNTID, 
            LNGAPDISCOUNTACCOUNTID, BLNISPAYDISCOUNT, BLNISQUANDISCOUNT, BLNISSALEDISCOUNT, BLNISALLITEM,
            STRITEMCODESTART, STRITEMCODEEND, STRCREATEDATE, STRCLOSEDATE, STRCUSTOMERAUXCODE, 
            LNGPRICEGRADEID, STRLICENCEDATE, STRBUSINESSLICENCEDATE, STRCHECKOUTDATE, BLNPREAPPROVE, 
            BLNCUSTOMERPREAPPROVE, BLNSUPPLYPREAPPROVE, STRLICENCEMSG, STRQUALITYPMSG, STRMOVEPHONE, 
            STRCARDNO, STRBIMID, BLNSHOWEINFORMATION, LNGACCOUNTSUBJECTTYPEID)
            select customer_seq.nextval, '"""+req["strcustomercode"]+"""', '"""+req["strcustomername"]+"""', '1', (select nvl(max(lngcustomertypeid),0) From customertype where blnisdetail=1 and blnisinactive=0), 
            0, 0, 0, ' ', 0, 
            100, 0, 0, ' ', 0, 
            ' ', ' ', ' ', ' ', ' ',
            nvl('"""+req["address"]+"""',' '), ' ', ' ', ' ', ' ', 
            ' ', '2022-06-26', 0, 560, 0, 
            0, 0, 0, 0, 1,
            ' ', ' ', '2022-06-26', ' ', ' ',
            0, '2037-04-10', '2024-12-29', ' ', 0,
            0, 0, ' ', ' ', ' ',
            ' ', ' ', 1, 7 from dual WHERE NOT EXISTS(SELECT 'X' FROM CUSTOMER WHERE STRCUSTOMERCODE='"""+req["strcustomercode"]+"""')"""
        strsql = """ select strcustomercode From public.customers where id<>"""+customers_id+""" and strcustomername='"""+req["strcustomername"]+"""' """
        try:
            result = models.Enums.get_selsql(strsql)
            if result is None :
                try:
                    self.update_model(customers, params)
                    models.db.session.commit()
                except :
                    abort(400, message="单位编号已存在.")
            else :
                abort(400, message="单位名称已存在.")
        except :
            abort(400, message="单位编号已存在.")
        strsql = """select  value from public.globalsetting where  type='接口' and code='合同单位回写HRP' and value='1'"""
        try:
            result = models.Enums.get_selsql(strsql)
            if result is None :
                return ;
        except :
            abort(400, message=strsql)
        strsql="""select  strcustomername from customer
        where strcustomercode = '"""+req["strcustomercode"]+"""'"""
        try:
            data_source = models.DataSource.get_by_name("jsphrp")
            result, error = data_source.query_runner.run_query(strsql, self.current_user)
            if error :
                return str(error);
            json_object = json.loads(result)
            if len(json_object["rows"])>0 :
                strsql=""" update customer set strcustomername='"""+req["strcustomername"]+"""' 
                where strcustomercode = '"""+req["strcustomercode"]+"""'"""
                data_source = models.DataSource.get_by_name("jsphrp")
                result, error = data_source.query_runner.run_query(strsql, self.current_user)
                return ;
        except :
            return;
        try:
            data_source = models.DataSource.get_by_name("jsphrp")
            result, error = data_source.query_runner.run_query(hrpsql, self.current_user)
            # if error:
            #     return str(error)+':'+hrpsql;
        except :
            return;

class CustomersdeleteResource(BaseResource):
    @require_default
    def post(self, customers_id):
        customers =  models.Customers.get_by_id(customers_id)
        strsql="select count(*) as nums From  public.project where customerid="+str(customers_id)
        isdelheck =models.enums.Enums.get_selsql(strsql)
        if isdelheck[0] >0:
            abort(400, message="单位已使用，不能删除！")
            return
        models.db.session.delete(customers)
        models.db.session.commit()



class CustomerListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        strcustomercode = req["strcustomercode"]
        strcustomername = req["strcustomername"]
        blnisinactive = req["blnisinactive"] if "blnisinactive" in req else "true"
        orderfield = req["field"] if "field" in req else ""
        userid = str(req["userid"]) if "userid" in req else ""

        if orderfield=="strcustomercode" :
            orderfield ="t1.strcustomercode"
        elif orderfield=="strcustomername":
            orderfield ="t1.strcustomername"
        elif orderfield=="representative":
            orderfield ="t1.representative"
        elif orderfield=="mobile":
            orderfield ="t1.mobile"
        elif orderfield=="address":
            orderfield ="t1.address"
        elif orderfield=="blnisinactive":
            orderfield ="t1.blnisinactive"
        else:
            orderfield ="t1.strcustomercode"
        order = req["order"] if "order" in req else 0

        strwhere ="t1.org_id=" +str(self.current_org.id)
        if strcustomercode !="" :
            strwhere = strwhere + " and t1.strcustomercode like '%"+strcustomercode+"%'"
        if strcustomername !='' :
            strwhere = strwhere + " and t1.strcustomername like '%"+strcustomername+"%'"
        if blnisinactive !="all" :
            strwhere = strwhere + " and t1.blnisinactive = "+blnisinactive

        if "super_admin" not in self.current_user.permissions and userid !='' :
            strwhere = strwhere + """ and  (t1.userid='""" + userid + """' or  t1.userid in (
                WITH RECURSIVE cte(keys,items,parentkeys) AS 
                (
                    select *From (with RECURSIVE le (keys,items,parentkeys) as
                    (select   (items)->>'key' as keys,json_array_elements((items)->'children') as items,'0' as parentkeys
                    from   (select unnest(details) items From orgstruct where orgstruct_enum='admin' and expire_date is null) AA
                    union all
                    select (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys from le)
                    select le.* from le inner join users on to_number(le.keys, '9999999999999999999')= users.orgunit_id where users.id='""" + userid + """') T1
                    UNION ALL  
                    SELECT  (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys FROM cte
                )
                SELECT users.id FROM cte inner join users on to_number((cte.items)->>'key', '9999999999999999999')=users.orgunit_id
                ))"""


        strgetfield =""" t1.*,t2.name as username """
        tblname = """public.customers t1 left join public.users t2 on t1.userid=t2.id """
        result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
        return result[0]

class DepartmentListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        code = req["code"]
        name = req["name"]
        userid = str(req["userid"])  if "userid" in req else "0"
        strsql ="""select id,code,
        case when (select COALESCE(max(value),'0') from public.globalsetting where  type='部门选择' and code='显示部门编码')='1' then code || ' ' || name else name end as name
            From public.orgunit where org_id=""" +str(self.current_org.id)
        if code !='' :
            strsql = strsql + " and code like '%"+code+"%'"
        if name !='' :
            strsql = strsql + " and name like '%"+name+"%'"
        strsql = strsql +' order by code,name '
        strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
        try:
            result = models.Enums.get_selsql(strsql)
            return result[0]
        except IntegrityError as e:
            logging.debug("DepartmentListResource: %s", str(e))
            abort(503, message= str(e))
    # def get(self,bytstatus):
    #   results = models.DepartmentTemp.get_by_status(bytstatus).order_by(models.DepartmentTemp.strdepartmentcode.asc())
    #   return [t.to_dict() for t in results]
####################################################################
class yusuancodeListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        code = req["code"] if "code" in req else "0"
        userid = str(req["userid"])  if "userid" in req else "0"
        contractid = str(req["contractid"])  if "contractid" in req else "0"
        strsql ="""select *From public.outlaybudgetview where org_id=""" +str(self.current_org.id) +""" 
         and 
        (strdepartmentcode in (select t1.code From public.orgunit t1 inner join public.users t2 on t1.id=t2.orgunit_id and t2.id="""+ userid+""")
        or code in(select budgetcode From public.contracts where id="""+ contractid+""")) order by code """
        strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
        try:
            result = models.Enums.get_selsql(strsql)
            return result[0]
        except IntegrityError as e:
            logging.debug("yusuancodeListResource: %s", str(e))
            abort(503, message= str(e))
    # def get(self,bytstatus):
    #   results = models.DepartmentTemp.get_by_status(bytstatus).order_by(models.DepartmentTemp.strdepartmentcode.asc())
    #   return [t.to_dict() for t in results]

####################################################################
class CustomerUserListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        code = req["code"]
        name = req["name"]
        userid = str(req["userid"])
        strsql ="""select t1.*
        from public.users t1 
        where t1.org_id=""" +str(self.current_org.id)
        if code !='' :
            strsql = strsql + " and t1.code like '%"+code+"%'"
        if name !='' :
            strsql = strsql + " and t1.name like '%"+name+"%'"

        strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """  order by t1.id) t """
        try:
            result = models.Enums.get_selsql(strsql)
            return result[0]
        except IntegrityError as e:
            logging.debug("CustomerUserListResource: %s", str(e))
            abort(503, message= str(e))

